﻿using System;
using System.Data;
using System.Data.SqlClient;
using QLTH_DTO;

namespace QLTH_DATASSECC
{
  public class clsGiaoVienDA : ConnectionData.clsKetNoi
    {
      String _sql;
      public void mo_excel(String __filename)
      {
          ketnoi_excel(__filename);
      }
      public DataTable DanhSachGiaoVienExcel()
      { 
          _sql ="SELECT * FROM [SHEET1$]";
          return TableExcel(_sql);
      }

      public DataTable DanhSachGiaoVien()
      {
          _sql = "SELECT GV.MAGV, (HOLOTGV+' '+TENGV) AS HOTEN, (CASE WHEN GV.PHAI = 0 THEN 'Nam' ELSE N'Nữ' END) AS PHAI,TCM.TOCHUYENMON, HV.TENHOCVI AS HOCVI,DT.TENDANTOC AS DANTOC, TG.TENTONGIAO AS TONGIAO,NGAYSINH , NOISINH , DIACHI, SDT, CMND"
                                    + " FROM GIAOVIEN GV INNER JOIN HOCVI HV ON GV.MAHV = HV.MAHV INNER JOIN DANTOC DT ON GV.MADT = DT.MADT INNER JOIN TONGIAO TG ON GV.MATG = TG.MATG INNER JOIN TOCHUYENMON TCM ON GV.MATCM = TCM.MATCM ORDER BY GV.MAGV";
          return Load_DataTable(_sql);
      }

      public DataTable DanhSachGiaoVienTheoDK(string dk)
      {
          String _dk = " WHERE freetext(GV.*,N'%" + dk + "%') ";
        
              _sql = "SELECT MAGV, (HOLOTGV+''+TENGV) AS HOTEN, (CASE WHEN GV.PHAI = 0 THEN 'NAM' ELSE N'Nữ' END) AS PHAI, HV.TENHOCVI AS HOCVI,DT.TENDANTOC AS DANTOC, TG.TENTONGIAO AS TONGIAO,NGAYSINH , NOISINH , DIACHI, SDT "
                                     + "FROM GIAOVIEN GV INNER JOIN HOCVI HV ON GV.MAHV = HV.MAHV INNER JOIN DANTOC DT ON GV.MADT = DT.MADT INNER JOIN TONGIAO TG ON GV.MATG = TG.MATG" + _dk;
          return Load_DataTable(_sql);
          
      }
      public DataTable LoadGV_HoTen(String _dk)
      {
          _sql = "SELECT MAGV, (HOLOTGV+''+TENGV) AS HOTEN FROM GIAOVIEN WHERE HOLOTGV like N'%"+ _dk + "%' OR TENGV like N'%" + _dk + "%' ";
          return Load_DataTable(_sql);
      }
      /// <summary>
      /// Thêm giáo viên
      /// </summary>
      /// <param name="gv"></param>
      /// <returns></returns>
      public int Them_giaovien(clsGiaoVienDTO gv)
      {
          _sql = "SET DATEFORMAT dmy;";
          int namepare = 14;
          String[] name = new String[namepare];
          Object[] values = new Object[namepare];
          name[0] = "@Magv"; values[0] = gv.Magv;
          name[1] = "@Mahv";values[1] =gv.Hocvi.Mahv;
          name[2] ="@Matcm" ;values[2] =gv.Tcm.Matcm;
          name[3] = "@Madt";values[3] =gv.dantoc.Madt;
          name[4] = "@Matg";values[4] =gv.tongiao.Matg;
          name[5] ="@Holot" ;values[5] =gv.Holot;
          name[6] = "@Ten";values[6] =gv.Ten;
          name[7] = "@Phai";values[7] =gv.Phai;
          name[8] = "@NgaySinh";values[8] =gv.Ngaysinh;
          name[9] = "@NoiSinh";values[9] =gv.Noisinh;
          name[10] = "@Diachi";values[10] =gv.Diachi;
          name[11] ="@Sdt" ;values[11] =gv.Sdt;
          name[12] = "@Anh";values[12] =gv.Anh;
          name[13] = "@Cmnd"; values[13] = gv.Cmnd;
          _sql += "INSERT INTO GIAOVIEN VALUES (@Magv,@Madt,@Mahv,@Matg,@Matcm,@Holot,@Ten,@Phai,@NgaySinh,@Cmnd,@NoiSinh,@DiaChi,@Sdt,@Anh)";
          return Nonquery(_sql,name,values,namepare);
      }

      public int Capnhat_giaovien(clsGiaoVienDTO gv)
      {
          int namepara = 14;
          String[] name = new String[namepara];
          Object[] values = new Object[namepara];
          name[0] = "@Magv"; values[0] = gv.Magv;
          name[1] = "@Mahv"; values[1] = gv.Hocvi.Mahv;
          name[2] = "@Matcm"; values[2] = gv.Tcm.Matcm;
          name[3] = "@Madt"; values[3] = gv.dantoc.Madt;
          name[4] = "@Matg"; values[4] = gv.tongiao.Matg;
          name[5] = "@Holot"; values[5] = gv.Holot;
          name[6] = "@Ten"; values[6] = gv.Ten;
          name[7] = "@Phai"; values[7] = gv.Phai;
          name[8] = "@NgaySinh"; values[8] = gv.Ngaysinh;
          name[9] = "@NoiSinh"; values[9] = gv.Noisinh;
          name[10] = "@Diachi"; values[10] = gv.Diachi;
          name[11] = "@Sdt"; values[11] = gv.Sdt;
          name[12] = "@Anh"; values[12] = gv.Anh;
          name[13] = "@Cmnd"; values[13] = gv.Cmnd;
          _sql += "UPDATE GIAOVIEN SET MAHV = @Mahv, MATCM = @Matcm, MADT = @Madt,MATG =@Matg,HOLOTGV =@Holot,TENGV =@Ten,PHAI =@Phai,NGAYSINH = @NgaySinh, DIACHI =@Diachi, NOISINH =@NoiSinh,SDT =@Sdt,ANH =@Anh, CMND = @Cmnd WHERE MAGV = @Magv";
          return Nonquery(_sql,name,values,namepara);
      }

      public int Xoa_giaovien(clsGiaoVienDTO gv)
      {
          int namepara = 1;
          String[] name = new String[namepara];
          Object[] values = new Object[namepara];
          name[0] = "@Magv"; values[0] = gv.Magv;
          _sql = "DELETE GIAOVIEN WHERE MAGV = @Magv";
          return Nonquery(_sql,name, values,namepara);
      }

      public DataTable lay_GV_theoma(String _magv)
      {
               _sql = "SELECT GV.MAGV, (HOLOTGV+' '+TENGV) AS HOTEN, (CASE WHEN GV.PHAI = 0 THEN 'Nam' ELSE N'Nữ' END) AS PHAI,TCM.TOCHUYENMON, HV.TENHOCVI AS HOCVI,DT.TENDANTOC AS DANTOC, TG.TENTONGIAO AS TONGIAO,NGAYSINH , NOISINH , DIACHI, SDT"
                                    +" FROM GIAOVIEN GV INNER JOIN HOCVI HV ON GV.MAHV = HV.MAHV INNER JOIN DANTOC DT ON GV.MADT = DT.MADT INNER JOIN TONGIAO TG ON GV.MATG = TG.MATG INNER JOIN TOCHUYENMON TCM ON TCM.MATCM = GV.MATCM"
                                    +" WHERE GV.MAGV ='"+_magv+"'";
             return Load_DataTable(_sql);
      }

      public string macuoi()
      {
          _sql = "SELECT TOP 1 MAGV FROM GIAOVIEN ORDER BY MAGV DESC";
          return laymacuoi(_sql);
      }


    }
}
